import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseInitializer {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    // 数据库名称
    private static final String DB_NAME = "student_management";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            stmt = conn.createStatement();

            System.out.println("开始创建数据库和表结构...");
            createDatabase(stmt);
            stmt.execute("USE " + DB_NAME);
            createTables(stmt);
            insertTestData(stmt);
            System.out.println("数据库和表结构创建成功！");

        } catch (ClassNotFoundException e) {
            System.err.println("JDBC驱动加载失败: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                System.err.println("资源关闭失败: " + e.getMessage());
            }
        }
    }

    private static void createDatabase(Statement stmt) throws SQLException {
        String sql = "CREATE DATABASE IF NOT EXISTS " + DB_NAME +
                " CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
        stmt.executeUpdate(sql);
        System.out.println("数据库 " + DB_NAME + " 创建成功");
    }

    private static void createTables(Statement stmt) throws SQLException {
        String createStudentTable = "CREATE TABLE IF NOT EXISTS student (" +
                "student_id VARCHAR(20) PRIMARY KEY, " +
                "name VARCHAR(50) NOT NULL, " +
                "gender VARCHAR(10), " +
                "class_name VARCHAR(50))";
        stmt.executeUpdate(createStudentTable);
        System.out.println("学生表创建成功");

        String createCourseTable = "CREATE TABLE IF NOT EXISTS course (" +
                "course_id VARCHAR(20) PRIMARY KEY, " +
                "course_name VARCHAR(100) NOT NULL, " +
                "credit INT)";
        stmt.executeUpdate(createCourseTable);
        System.out.println("课程表创建成功");

        String createGradeTable = "CREATE TABLE IF NOT EXISTS grade (" +
                "student_id VARCHAR(20), " +
                "course_id VARCHAR(20), " +
                "score DOUBLE, " +
                "PRIMARY KEY (student_id, course_id), " +
                "FOREIGN KEY (student_id) REFERENCES student(student_id), " +
                "FOREIGN KEY (course_id) REFERENCES course(course_id))";
        stmt.executeUpdate(createGradeTable);
        System.out.println("成绩表创建成功");
    }

    private static void insertTestData(Statement stmt) throws SQLException {
        try {
            // 先禁用外键约束检查
            stmt.execute("SET FOREIGN_KEY_CHECKS = 0");
            
            // 清空表数据（注意顺序，先清空有外键约束的表）
            stmt.executeUpdate("TRUNCATE TABLE grade");
            stmt.executeUpdate("TRUNCATE TABLE student");
            stmt.executeUpdate("TRUNCATE TABLE course");
            
            // 重新启用外键约束检查
            stmt.execute("SET FOREIGN_KEY_CHECKS = 1");

            // 插入学生测试数据
            String insertStudents = "INSERT INTO student VALUES " +
                    "('1001', '张三', '男', '计算机一班'), " +
                    "('1002', '李四', '女', '计算机一班'), " +
                    "('1003', '王五', '男', '计算机二班'), " +
                    "('1004', '赵六', '女', '计算机二班')";
            stmt.executeUpdate(insertStudents);
            System.out.println("学生测试数据插入成功");

            // 插入课程测试数据
            String insertCourses = "INSERT INTO course VALUES " +
                    "('C001', 'Java程序设计', 4), " +
                    "('C002', '数据库原理', 3), " +
                    "('C003', '数据结构', 4), " +
                    "('C004', '操作系统', 3)";
            stmt.executeUpdate(insertCourses);
            System.out.println("课程测试数据插入成功");

            // 插入成绩测试数据
            String insertGrades = "INSERT INTO grade VALUES " +
                    "('1001', 'C001', 85), " +
                    "('1001', 'C002', 78), " +
                    "('1002', 'C001', 92), " +
                    "('1002', 'C003', 88), " +
                    "('1003', 'C002', 76), " +
                    "('1003', 'C004', 90), " +
                    "('1004', 'C001', 58), " +
                    "('1004', 'C003', 82)";
            stmt.executeUpdate(insertGrades);
            System.out.println("成绩测试数据插入成功");

        } catch (SQLException e) {
            System.err.println("插入测试数据时出错: " + e.getMessage());
            throw e;
        }
    }
}